#!/usr/bin/python3
from datetime import datetime

import pymysql

# 打开数据库连接
db = pymysql.connect("172.17.40.112", "app_rw", "72uUvQQ8M9vx8H8h", "tianwen", 3306)

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = db.cursor()


def insertIntention(intention):
    insert_sql = "insert into intention(id,name,date_create,date_update) values(%s,%s,%s,%s)"
    cursor.execute(insert_sql, intention)
    id = cursor.lastrowid
    db.commit()
    return id


def insertSentences(sentences):
    insert_sql = "insert into sentence(word,intention_id,date_create,date_update) values(%s,%s,%s,%s)"
    for sen in sentences:
        cursor.execute(insert_sql, sen)
    db.commit()


def insertKeywords(keywords):
    insert_sql = "insert into keyword(keyword,intention_id,date_create,date_update) values(%s,%s,%s,%s)"
    for kw in keywords:
        cursor.execute(insert_sql, kw)
    db.commit()


def findAllIntnetions(path='/Users/dasouche/vueProjects/txt/intention.txt'):
    intentions = {}
    f = open(path, 'r')
    for i in range(700):
        print(i)
        line = f.readline().rstrip()
        if line == '':
            continue
        lines = line.split("	")
        key = lines[0]
        val = lines[1].rstrip()
        if intentions.get(key) is None:
            intentions[key] = []
        intentions[key].append(val)
    return intentions


def main():
    try:
        intentions = findAllIntnetions()
        for key in intentions:
            val = intentions[key]
            name = val[0]
            dt = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            key = int(key) + 1
            # row_id = insertIntention((key, name, dt, dt))
            # print(row_id)
            sentences = []
            for word in val:
                sentences.append((word, key, dt, dt))
            # insertSentences(sentences)
            insertKeywords(sentences)

    except Exception as e:
        # 如果发生错误则回滚
        db.rollback()
        print(e)
    finally:
        # 关闭数据库连接
        db.close()


if __name__ == "__main__":
    main()
